﻿-- ===============================================================================================
-- General Deletetions
-- ===============================================================================================

DELETE FROM [esf_sso].[UserSessionLog];
DELETE FROM [esf_sso].[Person_Location];
DELETE FROM [esf_sso].[Location];
DELETE FROM [esf_sso].[User_BasicReference];
DELETE FROM [esf_sso].[User];
DELETE FROM [esf_sso].[Person];
DELETE FROM [esf_sso].[Enterprise];
DELETE FROM [esf_sso].[TreeBasicReference];
DELETE FROM [esf_sso].[BasicReference];
DELETE FROM [esf_sso].[BasicReferenceType];
GO

-- ===============================================================================================
-- Basic Reference Types
-- ===============================================================================================

DELETE FROM [esf_sso].[BasicReferenceType];
GO

SET IDENTITY_INSERT [esf_sso].[BasicReferenceType] ON

INSERT INTO [esf_sso].[BasicReferenceType] (ID, [Name])
-- 1
SELECT 1, 'Tipo de Identificación'
UNION ALL
-- 2
SELECT 2, 'Genero'
UNION ALL
-- 3
SELECT 3, 'Pregunta de Seguridad'
UNION ALL
-- 4
SELECT 4, 'Aplicaciones'
UNION ALL
-- 5
SELECT 5, 'Grupos de Usuario'
UNION ALL
-- 6
SELECT 6, 'Entidades'
-- 7
UNION ALL
SELECT 7, 'Menus de Usuario'
-- 8
UNION ALL
SELECT 8, 'Paises'
-- 9
UNION ALL
SELECT 9, 'Departamentos'
-- 10
UNION ALL
SELECT 10, 'Ciudades'
-- 11
UNION ALL
SELECT 11, 'Terminales'
-- 12
UNION ALL
SELECT 12, 'Categorias de Imagen'
-- 13
UNION ALL
SELECT 13, 'Permisos de Usuario'
-- 14
UNION ALL
SELECT 14, 'Categorias de Contactenos';

SET IDENTITY_INSERT [esf_sso].[BasicReferenceType] OFF
GO


-- ===============================================================================================
-- Basic References
-- ===============================================================================================

DELETE FROM [esf_sso].[BasicReference];
GO

INSERT INTO [esf_sso].[BasicReference] ([BasicReferenceTypeID],[Code],[Name],[Description],[IsEnabled],[CreationDateTime], UserCreationAlias)

-- Identification Types

SELECT 1, 'CC', 'Cedula de Ciudadanía', NULL, 1, GETDATE (), 'root'
UNION ALL
SELECT 1, 'TI', 'Tarjeta de Identidad', NULL, 1, GETDATE (), 'root'
UNION ALL
SELECT 1, 'P', 'Pasaporte', NULL, 1, GETDATE (), 'root'
UNION ALL
SELECT 1, 'CE', 'Cedula de Extranjería', NULL, 1, GETDATE (), 'root'
UNION ALL
SELECT 1, 'RC', 'Registro Civil', NULL, 1, GETDATE (), 'root'
UNION ALL
SELECT 1, 'NIT', 'Número de Identificación Tributario', NULL, 1, GETDATE (), 'root'

-- Gender

UNION ALL
SELECT 2, 'M', 'Masculino', NULL, 1, GETDATE (), 'root'
UNION ALL
SELECT 2, 'F', 'Femenino', NULL, 1, GETDATE (), 'root'

-- Security Questions

UNION ALL
SELECT 3, 'SQ1', 'Cual es el nombre de su primer mascota?', NULL, 1, GETDATE (), 'root'
UNION ALL
SELECT 3, 'SQ2', 'Cual es el nombre de su primer novia/o?', NULL, 1, GETDATE (), 'root'
UNION ALL
SELECT 3, 'SQ3', 'Cual es el nombre del hospital donde nacio?', NULL, 1, GETDATE (), 'root'
UNION ALL
SELECT 3, 'SQ4', 'Cual es su estatura?', NULL, 1, GETDATE (), 'root'
UNION ALL
SELECT 3, 'SQ5', 'Cual es su color favorito?', NULL, 1, GETDATE (), 'root'

-- Applications (4)

-- En la inicializacion de cada aplicacion se llena

-- User Groups

UNION ALL
SELECT 5, 'Administrators', 'Administradores', NULL, 1, GETDATE (), 'root'
UNION ALL
SELECT 5, 'Public', 'Publico', NULL, 1, GETDATE (), 'root'


-- Gender

--UNION ALL
--SELECT 12, 'Icon_16x16', 'Icono 16x16', NULL, 1, GETDATE (), 'root'
--UNION ALL
--SELECT 12, 'Header_400x50', 'Encabezado 400x50', NULL, 1, GETDATE (), 'root'

-- ContactUs Type

UNION ALL
SELECT 14, 'CT1', 'Solicitud de Material Externo', NULL, 1, GETDATE (), 'root'
UNION ALL
SELECT 14, 'CT2', 'Comentarios', NULL, 1, GETDATE (), 'root'
UNION ALL
SELECT 14, 'CT3', 'Queja o Reclamo', NULL, 1, GETDATE (), 'root'
UNION ALL
SELECT 14, 'CT4', 'Reportar un Error en la Web', NULL, 1, GETDATE (), 'root'
UNION ALL
SELECT 14, 'CT5', 'Sugerencias', NULL, 1, GETDATE (), 'root'

GO


-- ===============================================================================================
-- Enterprises (ref BasicReference)
-- ===============================================================================================

DECLARE @EnterpriseID AS BIGINT

EXEC [esf_sso].P_InsertEnterprise @EnterpriseID OUT, '80093116', 'NIT', '5', NULL, 'Edilberto Sanchez Forero'



-- ===============================================================================================
-- Usuarios
-- ===============================================================================================

DECLARE @RootUserID BIGINT


SET @RootUserID = (SELECT PersonID FROM [esf_sso].[User] WHERE [Alias] = 'root')

IF @RootUserID IS NULL
BEGIN

	DECLARE @PersonID AS BIGINT
	

	INSERT INTO [esf_sso].[Person]
		([FirstName],[SecondName],[SurName],[LastName],[BirthDate]
		,[GenderID],[IdentificationTypeID]
		,[IdentificationNumber],[Email]
		,[EnterpriseID],[OtherEnterprise])
	SELECT
		'Edilberto', NULL, 'Sánchez', 'Forero', '19811110'
		,(SELECT ID FROM [esf_sso].BasicReference WHERE Code = 'M' AND BasicReferenceTypeID = 2)
		,(SELECT ID FROM [esf_sso].BasicReference WHERE Code = 'CC' AND BasicReferenceTypeID = 1)
		,'80093116', 'esanchezfo@gmail.com'
		,NULL
		,NULL
	
	
	SET @PersonID = @@IDENTITY
	

	INSERT INTO [esf_sso].[User] 
		([PersonID],[Alias],[Password],[IsEnabled],
		SecurityQuestionID,SecurityQuestionAnswer,[CreationDateTime], UserCreationAlias)
	SELECT @PersonID, 'root', 'T9zCVq+yadoX0C1XTobhnA==', 1, 
		(SELECT ID FROM [esf_sso].BasicReference WHERE Code = 'SQ1' AND BasicReferenceTypeID = 3)
		,'ESF'
		,GETDATE (), 'root'
	
	INSERT INTO [esf_sso].[User_BasicReference] (UserPersonID, BasicReferenceID)
	SELECT @PersonID, (SELECT ID FROM esf_sso.VUserGroup WHERE Code = 'Administrators')
	
END


UPDATE [esf_sso].[Person] 
SET EnterpriseID = @EnterpriseID
WHERE ID = @RootUserID

GO

-- Terminal de prueba

--DECLARE @xID INT
--DECLARE @now DATETIME

--SET @now = GETDATE ()

-- EXEC esf_sso.P_InsertBasicReference @xID OUT, 11, '9102030', '9102030', 'Nurit Lipman', NULL, NULL, NULL, 1, 'root', @now